import openpyxl
import pymysql

class DBPipeline:
    def __init__(self):
        self.conn = pymysql.connect(host='127.0.0.1',port=3306,
                                    user='root', password='employment_analysis',
                                    database='spider', charset='utf8mb4')
        self.cursor = self.conn.cursor()
    def open_spider(self, spider):
        pass

    def close_spider(self, spider):
        # 提交事务(此时才会插入数据库)
        self.conn.commit()
        # 关闭连接
        self.conn.close()
    def process_item(self, item, spider):
        # 没有数据则保存为空串
        author = item.get('author', '')
        repository = item.get('repository', '')
        desc = item.get('desc', '')
        language = item.get('language', '')
        repository_url = item.get('repository_url', '')
        clone_url = item.get('clone_url', '')

        self.cursor.execute(
            'insert into gitee (author, repository, `desc`, language, repository_url, clone_url) values (%s, %s, %s, %s, %s, %s)',
            (author, repository, desc, language, repository_url, clone_url))

        return item


class GiteeScrapyPipeline:
    def __init__(self):
        # excel文件对象
        self.Workbook = openpyxl.Workbook()
        # 工作表对象
        self.workSheet = self.Workbook.active
        self.workSheet.append(('作者', '仓库名', '描述', '分类', '仓库地址', '克隆地址'))

    # 管道钩子方法
    def open_spider(self, spider):
        pass

    def close_spider(self, spider):
        self.Workbook.save('gitee推荐项目.xlsx')

    def process_item(self, item, spider):
        author = item.get('author', '')
        repository = item.get('repository', '')
        desc = item.get('desc', '')
        language = item.get('language', '')
        repository_url = item.get('repository_url', '')
        clone_url = item.get('clone_url', '')
        self.workSheet.append((author, repository, desc, language, repository_url, clone_url))
        return item
